package com.sinotrans.common.util.db;

import java.io.File;
import java.io.FileWriter;
import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

/**
 * mysql数据表转换为 java 的 entity mapper repository 工具
 * <p/>
 * 注意, 部分功能需要 mysql information_schema 数据库的查询权限
 * 
 */
public class MysqlTableToJava {
	// 作者名字
	private final static String authorName = "MysqlTableToJava";
	public final static String Entity = "entity";
	public final static String Mapper = "mapper";
	public final static String Repository = "repository";

	private String jdbcUrl;
	private String jdbcUser;
	private String jdbcPassword;
	private String jdbcDriver;

	private Connection connection;

	/**
	 * 构造函数
	 * 
	 * @param jdbcUrl      数据库地址
	 * @param jdbcUser     数据库账号
	 * @param jdbcPassword 数据库密码
	 * @param jdbcDriver   数据库驱动
	 */
	public MysqlTableToJava(String jdbcUrl, String jdbcUser, String jdbcPassword, String jdbcDriver) {
		this.jdbcUrl = jdbcUrl;
		this.jdbcUser = jdbcUser;
		this.jdbcPassword = jdbcPassword;
		this.jdbcDriver = jdbcDriver;
		initConnection();
	}

	private void initConnection() {
		// 创建连接
		try {
			Class.forName(jdbcDriver);
			connection = DriverManager.getConnection(jdbcUrl, jdbcUser, jdbcPassword);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	public void close() {
		if (connection != null) {
			try {
				if (!connection.isClosed()) {
					connection.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	private String getSchema() {
		int i = jdbcUrl.lastIndexOf("/");
		int w = jdbcUrl.indexOf("?");
		if (w == -1) {
			w = jdbcUrl.length();
		}
		return jdbcUrl.substring(i + 1, w);
	}

	/**
	 * 查询所有数据表名备注map
	 * <p/>
	 * 注意: 需要 mysql information_schema 数据库的查询权限
	 * 
	 * @return
	 */
	private Map<String, String> dbAllTableNameRemarkMap() {
		Map<String, String> dbAllTableNameRemarkMap = new HashMap<>();

		// 查要生成实体类的表
		String sql = "select table_name, table_comment from information_schema.tables where table_schema=";
		PreparedStatement pStemt = null;
		ResultSet rs = null;
		try {
			sql += "'" + getSchema() + "'";
			pStemt = connection.prepareStatement(sql);
			rs = pStemt.executeQuery();
			while (rs.next()) {
				dbAllTableNameRemarkMap.put(rs.getString(1), rs.getString(2));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (pStemt != null) {
				try {
					pStemt.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		return dbAllTableNameRemarkMap;
	}

	/**
	 * 数据表字段信息
	 * 
	 * @param tableName
	 * @return
	 */
	private List<ColumnEntity> dbTableColumnInfo(String tableName) {
		List<ColumnEntity> list = new ArrayList<>();
		// 查要生成实体类的表
		String sql = "show full columns from " + tableName;
		PreparedStatement pStemt = null;
		ResultSet rs = null;
		try {
			pStemt = connection.prepareStatement(sql);
			rs = pStemt.executeQuery();
			while (rs.next()) {
				ColumnEntity ce = new ColumnEntity();
				ce.setName(rs.getString("Field"));
				String type = rs.getString("Type");
				String remarks = rs.getString("Comment");
				String key = rs.getString("Key");
				if (key != null && key.toLowerCase().contains("pri")) {
					ce.setPrimaryKey(true);
				} else {
					ce.setPrimaryKey(false);
				}
				ce.setDataType(parseEntityType(type));
				ce.setRemarks(remarks);
				list.add(ce);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (pStemt != null) {
				try {
					pStemt.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		return list;
	}

	/**
	 * mysql数据表转换为 java 的 entity mapper repository
	 * <p/>
	 * 注意: 需要 mysql information_schema 数据库的查询权限
	 * 
	 * @param javaPackage java类包名, 如: com.sinotrans
	 */
	public void allTableToJava(String javaPackage) {
		Map<String, String> dbAllTableNameRemarkMap = dbAllTableNameRemarkMap();
		for (Entry<String, String> nr : dbAllTableNameRemarkMap.entrySet()) {
			tableToEntity(nr.getKey(), nr.getValue(), javaPackage);
			tableToMapper(nr.getKey(), nr.getValue(), javaPackage);
			tableToRepository(nr.getKey(), nr.getValue(), javaPackage);
			tableToRepositoryImpl(nr.getKey(), nr.getValue(), javaPackage);
		}
	}

	/**
	 * mysql数据表转换为java实体
	 * 
	 * @param tableName 数据表名
	 * @param remark    数据表备注
	 * @param Entity    java实体类包名, 如: com.sinotrans.entity
	 */
	protected String tableToEntity(String tableName, String remark, String javaPackage) {
		String name = replaceUnderlineAndfirstToUpper(tableName.toLowerCase(), "_", "") + initcap(Entity);
		List<ColumnEntity> list = dbTableColumnInfo(tableName);
		String content = entityParse(tableName, remark, list, javaPackage);
		return createJava(javaPackage + "." + Entity, name, content);
	}

	/**
	 * mysql数据表转换为java实体
	 * 
	 * @param tableName 数据表名
	 * @param remark    数据表备注
	 * @param Mapper    java实体类包名, 如: com.sinotrans.entity
	 */
	protected String tableToMapper(String tableName, String remark, String javaPackage) {
		String name = replaceUnderlineAndfirstToUpper(tableName.toLowerCase(), "_", "") + initcap(Mapper);
		String content = mapperParse(tableName, remark, javaPackage);
		return createJava(javaPackage + "." + Mapper, name, content);
	}

	/**
	 * mysql数据表转换为java持久层
	 * 
	 * @param tableName 数据表名
	 * @param remark    数据表备注
	 * @param Mapper    java实体类包名, 如: com.sinotrans.entity
	 */
	protected String tableToRepository(String tableName, String remark, String javaPackage) {
		String name = replaceUnderlineAndfirstToUpper(tableName.toLowerCase(), "_", "") + initcap(Repository);
		String content = repositoryParse(tableName, remark, javaPackage);
		return createJava(javaPackage + "." + Repository, name, content);
	}

	/**
	 * mysql数据表转换为java持久层
	 * 
	 * @param tableName 数据表名
	 * @param remark    数据表备注
	 * @param Mapper    java实体类包名, 如: com.sinotrans.entity
	 */
	protected String tableToRepositoryImpl(String tableName, String remark, String javaPackage) {
		String name = replaceUnderlineAndfirstToUpper(tableName.toLowerCase(), "_", "") + initcap(Repository) + "Impl";
		String content = repositoryImplParse(tableName, remark, javaPackage);
		return createJava(javaPackage + "." + Repository + ".impl", name, content);
	}

	/**
	 * 创建java文件
	 * 
	 * @param javaPackage java包名
	 * @param javaName    java类名
	 * @param javaContent 内容
	 * @return
	 */
	protected String createJava(String javaPackage, String javaName, String javaContent) {
		String javaClass = javaPackage + "." + javaName;
		try {
			File directory = new File("");
			String outputPath = directory.getAbsolutePath() + "/src/main/java/" + javaClass.replace(".", "/") + ".java";
			File file = new File(outputPath);
			System.out.println("----------->" + outputPath);
			if (!file.exists()) {
				if (!file.getParentFile().exists()) {
					try {
						file.getParentFile().mkdirs(); // 不存在则创建父目录
					} catch (Exception e) {
						e.printStackTrace();
					}
				}
				file.createNewFile();
			}
			FileWriter fw = new FileWriter(outputPath);
			PrintWriter pw = new PrintWriter(fw);
			pw.println(javaContent);
			pw.flush();
			pw.close();
		} catch (IOException e) {
			e.printStackTrace();
		}
		return javaClass;
	}

	private String entityParse(String tableName, String tableRemarks, List<ColumnEntity> list, String javaPackage) {
		StringBuffer sb = new StringBuffer();
		sb.append("package " + javaPackage + "." + Entity + ";\r\n");
		sb.append("\r\n");
		sb.append("import com.baomidou.mybatisplus.annotation.TableField;\r\n");
		sb.append("import com.baomidou.mybatisplus.annotation.TableId;\r\n");
		sb.append("import com.baomidou.mybatisplus.annotation.TableName;\r\n");
		sb.append("import java.io.Serializable;\r\n");
		sb.append("import java.util.Date;\r\n");

		sb.append("import io.swagger.annotations.ApiModel;\r\n");
		sb.append("import io.swagger.annotations.ApiModelProperty;\r\n");
		sb.append("\r\n");
		// 注释部分
		infoMsg(sb, tableRemarks + "实体类");
		// 实体部分
		sb.append("@ApiModel(description = \"" + tableRemarks + "实体\")\r\n");
		sb.append("@TableName(value = \"" + tableName + "\")\r\n");
		sb.append("public class " + replaceUnderlineAndfirstToUpper(tableName.toLowerCase(), "_", "") + initcap(Entity)
				+ " implements Serializable" + "{\r\n");
		sb.append("\tprivate static final long serialVersionUID = " + System.currentTimeMillis() + "L;\r\n");
		sb.append("\r\n");
		entityAddElement(sb, list);
		sb.append("}\r\n");

		return sb.toString();
	}

	private String mapperParse(String tableName, String tableRemarks, String javaPackage) {
		String javaName = replaceUnderlineAndfirstToUpper(tableName.toLowerCase(), "_", "");
		StringBuffer sb = new StringBuffer();
		sb.append("package " + javaPackage + "." + Mapper + ";\r\n");
		sb.append("\r\n");
		sb.append("import org.springframework.stereotype.Repository;\r\n");
		sb.append("import com.baomidou.mybatisplus.core.mapper.BaseMapper;\r\n");
		sb.append("import " + javaPackage + "." + Entity + "." + javaName + initcap(Entity) + ";\r\n");
		sb.append("\r\n");
		// 注释部分
		infoMsg(sb, tableRemarks + " mapper");
		// 实体部分
		sb.append("@Repository\r\n");
		sb.append("public interface " + javaName + initcap(Mapper) + " extends BaseMapper<" + javaName + initcap(Entity)
				+ "> {\r\n");
		sb.append("\r\n");
		sb.append("}\r\n");

		return sb.toString();
	}

	private String repositoryParse(String tableName, String tableRemarks, String javaPackage) {
		String javaName = replaceUnderlineAndfirstToUpper(tableName.toLowerCase(), "_", "");
		StringBuffer sb = new StringBuffer();
		sb.append("package " + javaPackage + "." + Repository + ";\r\n");
		sb.append("\r\n");
		sb.append("import com.baomidou.mybatisplus.extension.service.IService;\r\n");
		sb.append("import " + javaPackage + "." + Entity + "." + javaName + initcap(Entity) + ";\r\n");
		sb.append("\r\n");
		// 注释部分
		infoMsg(sb, tableRemarks + "持久层");
		sb.append("public interface " + javaName + initcap(Repository) + " extends IService<" + javaName
				+ initcap(Entity) + "> {\r\n");
		sb.append("\r\n");
		sb.append("}\r\n");
		return sb.toString();
	}

	private String repositoryImplParse(String tableName, String tableRemarks, String javaPackage) {
		String javaName = replaceUnderlineAndfirstToUpper(tableName.toLowerCase(), "_", "");
		StringBuffer sb = new StringBuffer();
		sb.append("package " + javaPackage + "." + Repository + ".impl;\r\n");
		sb.append("\r\n");
		sb.append("import org.springframework.stereotype.Repository;\r\n");
		sb.append("import org.springframework.transaction.annotation.Transactional;\r\n");
		sb.append("import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;\r\n");
		sb.append("import " + javaPackage + "." + Entity + "." + javaName + initcap(Entity) + ";\r\n");
		sb.append("import " + javaPackage + "." + Mapper + "." + javaName + initcap(Mapper) + ";\r\n");
		sb.append("import " + javaPackage + "." + Repository + "." + javaName + initcap(Repository) + ";\r\n");
		sb.append("\r\n");
		// 注释部分
		infoMsg(sb, tableRemarks + "持久层");
		sb.append("@Repository\r\n");
		sb.append("@Transactional\r\n");
		sb.append("public class " + javaName + initcap(Repository) + "Impl extends ServiceImpl<" + javaName
				+ initcap(Mapper) + ", " + javaName + initcap(Entity) + "> implements " + javaName + initcap(Repository)
				+ " {\r\n");
		sb.append("\r\n");
		sb.append("}\r\n");
		return sb.toString();
	}

	protected static void entityAddElement(StringBuffer sb, List<ColumnEntity> list) {
		// 字段
		for (ColumnEntity ce : list) {
			sb.append("\t@ApiModelProperty(value = \"" + ce.getRemarks() + "\")\r\n");
			if (ce.isPrimaryKey()) {
				sb.append("\t@TableId(value = \"" + ce.getName() + "\")\r\n");
			} else {
				sb.append("\t@TableField(value = \"" + ce.getName() + "\")\r\n");
			}
			sb.append("\tprivate " + ce.getDataType() + " " + lineToHump(ce.getName()) + ";\r\n");
			sb.append("\r\n");
		}

		// get set
		for (ColumnEntity ce : list) {
			// get
			sb.append("\tpublic " + ce.getDataType() + " get" + initcap(lineToHump(ce.getName())) + "() {\r\n");
			sb.append("\t\treturn " + lineToHump(ce.getName()) + ";\r\n");
			sb.append("\t}\r\n");
			sb.append("\r\n");

			// set
			sb.append("\tpublic void set" + initcap(lineToHump(ce.getName())) + "(" + ce.getDataType() + " "
					+ lineToHump(ce.getName()) + ") {\r\n");
			sb.append("\t\tthis." + lineToHump(ce.getName()) + " = " + lineToHump(ce.getName()) + ";\r\n");
			sb.append("\t}\r\n");
			sb.append("\r\n");
		}

	}

	private static String parseEntityType(String type) {
		if (type.toLowerCase().contains("int")) {
			return "Integer";
		}
		if (type.toLowerCase().contains("varchar")) {
			return "String";
		}
		if (type.toLowerCase().contains("datetime")) {
			return "Date";
		}
		if (type.toLowerCase().contains("text")) {
			return "String";
		}
		if (type.toLowerCase().contains("double")) {
			return "Double";
		}
		if (type.toLowerCase().contains("decimal")) {
			return "Integer";
		}
		if (type.toLowerCase().contains("number")) {
			return "Integer";
		}
		if (type.toLowerCase().contains("char")) {
			return "Character";
		}
		return null;
	}

	private static Pattern linePattern = Pattern.compile("_(\\w)");

	/** 下划线转驼峰 */
	protected static String lineToHump(String str) {
		str = str.toLowerCase();
		Matcher matcher = linePattern.matcher(str);
		StringBuffer sb = new StringBuffer();
		while (matcher.find()) {
			matcher.appendReplacement(sb, matcher.group(1).toUpperCase());
		}
		matcher.appendTail(sb);
		return sb.toString();
	}

	private static Pattern humpPattern = Pattern.compile("[A-Z]");

	/** 驼峰转下划线 */
	protected static String humpToLine(String str) {
		Matcher matcher = humpPattern.matcher(str);
		StringBuffer sb = new StringBuffer();
		while (matcher.find()) {
			matcher.appendReplacement(sb, "_" + matcher.group(0).toLowerCase());
		}
		matcher.appendTail(sb);
		return sb.toString();
	}

	/**
	 * 自动生成注释
	 * 
	 * @title
	 * @date 2014-10-17
	 * @author ZhengYunfei
	 */
	protected static void infoMsg(StringBuffer sb, String msg) {
		sb.append("/**\r\n");
		sb.append(" * @author " + authorName + "\r\n");
		sb.append(" * @Description " + msg + "\r\n");
		sb.append(" */\r\n");
	}

	/**
	 * 首字母大写
	 *
	 * @param srcStr
	 * @return
	 */
	protected static String firstCharacterToUpper(String srcStr) {
		return srcStr.substring(0, 1).toUpperCase() + srcStr.substring(1);
	}

	/**
	 * 替换字符串并让它的下一个字母为大写
	 * 
	 * @param srcStr
	 * @param org
	 * @param ob
	 * @return
	 */
	protected static String replaceUnderlineAndfirstToUpper(String srcStr, String org, String ob) {
		String newString = "";
		int first = 0;
		while (srcStr.indexOf(org) != -1) {
			first = srcStr.indexOf(org);
			if (first != srcStr.length()) {
				newString = newString + srcStr.substring(0, first) + ob;
				srcStr = srcStr.substring(first + org.length(), srcStr.length());
				srcStr = firstCharacterToUpper(srcStr);
			}
		}
		newString = firstCharacterToUpper(newString + srcStr);
		return newString;
	}

	/**
	 * 功能：将输入字符串的首字母改成大写
	 * 
	 * @param str
	 * @return
	 */
	private static String initcap(String str) {

		char[] ch = str.toCharArray();
		if (ch[0] >= 'a' && ch[0] <= 'z') {
			ch[0] = (char) (ch[0] - 32);
		}

		return new String(ch);
	}

	/**
	 * 数据表字段实体
	 * 
	 * @author ligaofu
	 *
	 */
	private class ColumnEntity {
		/**
		 * 是否主键
		 */
		private boolean primaryKey;
		/**
		 * 字段名称
		 */
		private String name;
		/**
		 * 字段类型
		 */
		private String dataType;
		/**
		 * 字段备注
		 */
		private String remarks;

		public boolean isPrimaryKey() {
			return primaryKey;
		}

		public void setPrimaryKey(boolean primaryKey) {
			this.primaryKey = primaryKey;
		}

		public String getName() {
			return name;
		}

		public void setName(String name) {
			this.name = name;
		}

		public String getDataType() {
			return dataType;
		}

		public void setDataType(String dataType) {
			this.dataType = dataType;
		}

		public String getRemarks() {
			return remarks;
		}

		public void setRemarks(String remarks) {
			this.remarks = remarks;
		}

	}

	public String getJdbcUrl() {
		return jdbcUrl;
	}

	public void setJdbcUrl(String jdbcUrl) {
		this.jdbcUrl = jdbcUrl;
	}

	public String getJdbcUser() {
		return jdbcUser;
	}

	public void setJdbcUser(String jdbcUser) {
		this.jdbcUser = jdbcUser;
	}

	public String getJdbcPassword() {
		return jdbcPassword;
	}

	public void setJdbcPassword(String jdbcPassword) {
		this.jdbcPassword = jdbcPassword;
	}

	public String getJdbcDriver() {
		return jdbcDriver;
	}

	public void setJdbcDriver(String jdbcDriver) {
		this.jdbcDriver = jdbcDriver;
	}

}